package com.chidopi.market.bookstore.service.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.chidopi.cdf.service.impl.CommonServiceImpl;
import com.chidopi.market.bookstore.service.IDownloadStatService;

public class DownloadStatServiceImpl extends CommonServiceImpl implements IDownloadStatService {
	private static Logger logger = LoggerFactory.getLogger(DownloadStatServiceImpl.class);
	
	@Override
	public List<Map<String, Object>> queryDownloadStat(HttpServletRequest request) {
		String sql = "select ifnull(c.cityname, '(Total)') as cityname, ifnull(b.bookname, '(Total)') as bookname, date_format(starttime, '%Y-%m-%d') as downtime, count(*) as times, sum(ifnull(a.booksize,0)) as quantity from zipdownlog a, bookitems b, bookcities c where a.book_id=b.id and a.city_id=c.id" + this.getWhere(request) + " group by c.cityname, b.bookname, date_format(starttime, '%Y-%m-%d') with rollup";
		logger.debug(sql);
		try {
			return super.queryForList(sql);
		} catch (Exception e) {
			logger.warn("queryDownloadStat", e);
			return null;
		}
	}

	@Override
	public List<Map<String, Object>> queryDownloadDetail(HttpServletRequest request) {
		String sql = "select c.cityname, b.bookname, date_format(starttime, '%Y/%m/%d %H:%i:%s') as starttime, ifnull(from_ip, 'none') as from_ip, case free_flag when '0' then 'free' else 'paid' end as free_flag , b.booksize from zipdownlog a, bookitems b, bookcities c where a.book_id=b.id and a.city_id=c.id" + this.getWhere(request);
		logger.debug(sql);
		try {
			return super.queryForList(sql);
		} catch (Exception e) {
			logger.warn("queryDownloadStat", e);
			return null;
		}
	}
	
	private String getWhere(HttpServletRequest request) {
		String sql = "";
		String city_id = request.getParameter("city_id");
		if (city_id != null && city_id.length() > 0) {
			sql += " and c.id=" + city_id;
		}
		String bookname = request.getParameter("bookname");
		if (bookname != null && bookname.length() > 0) {
			sql += " and b.bookname regexp '" + bookname + "'";
		}
//		String free_flag = request.getParameter("free_flag");
//		if (free_flag != null && free_flag.length() > 0) {
//			sql += " and a.free_flag='" + free_flag + "'";
//		}
		String[] sa = request.getParameterValues("free_flag");
		if (sa != null && sa.length > 0) {
			if (sa.length == 1)
				sql += " and a.free_flag='" + sa[0] + "'";
			else
				sql += " and a.free_flag in (" + Arrays.asList(sa).toString().replaceAll("^\\[|\\]$", "") + ")";
		}
		String from_ip = request.getParameter("from_ip");
		if (from_ip != null && from_ip.length() > 0) {
			sql += " and a.from_ip regexp '" + from_ip + "'";
		}
		String start_date = request.getParameter("start_date");
		String end_date = request.getParameter("end_date");
		logger.debug(start_date + " ~ " + end_date);
		if (start_date != null && from_ip.length() > 0 ) {
			sql += " and  date_format(a.starttime, '%Y/%m/%d') >= '" + start_date + "'";
		}
		
		if (end_date != null && end_date.length() > 0 ) {
			sql += " and  date_format(a.endtime, '%Y/%m/%d') <= '" + end_date + "'";
		}
		return sql;
	}
}
